package com.tpp.mapper;

import com.tpp.entity.Film;

import com.tpp.entity.FilmItemShow;
import com.tpp.entity.FilmTime;
import com.tpp.entity.Order;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.sql.Date;
import java.util.List;

@Mapper
@Repository
public interface FilmMapper {
    @Select("SELECT f.* ,b.Url \n" +
            "FROM  film_info f JOIN film_broadcast_info b\n" +
            "ON f.Film_id=b.Film_id\n" +
            "WHERE f.Film_name= #{0}")
    Film GetFilmInfo(@Param("0") String Film_name);

    @Select("SELECT distinct Film_date\n" +
            "\t\tFROM film_times f JOIN hall_info h\n" +
            "\t\tON f.Hall_id=h.Hall_id\n" +
            "\t\tWHERE h.Cinema_id in(\n" +
            "\t\t\tSELECT Cinema_id\n" +
            "\t\tFROM cinema_info\n" +
            "\t\tWHERE Cinema_name=#{0}\n" +
            "\t\t) AND f.Film_id=(\n" +
            "\t\tSELECT Film_id\n" +
            "\t\tFROM film_info\n" +
            "\t\tWHERE Film_name=#{1}\n" +
            "\t\t)")
    Date[] FindAFilmTimeByCinema(@Param("0")String cinema,@Param("1")String filmTitle);
    @Select("SELECT Film_times_id ,Film_time , Language_version,Hall_name,Seat_info,Film_price\n" +
            "FROM film_times JOIN hall_info\n" +
            "on film_times.Hall_id=hall_info.Hall_id\n" +
            "WHERE Film_date=#{0} and Film_id=(\n" +
            "SELECT Film_id\n" +
            "FROM film_info\n" +
            "WHERE Film_name=#{1}\n" +
            ")")
    List<FilmTime> FindAFilmTimeByDate(@Param("0")Date date,@Param("1") String Film_name);
    @Select("SELECT Film_times_id ,Film_time , Language_version,Hall_name,Seat_info,Film_price\n" +
            "FROM film_times JOIN hall_info\n" +
            "on film_times.Hall_id=hall_info.Hall_id\n" +
            "WHERE  Film_id=(\n" +
            "SELECT Film_id\n" +
            "FROM film_info\n" +
            "WHERE Film_name=#{1}\n" +
            ")")
    List<FilmTime> FindAFilmTimeByTitle(@Param("1") String Film_name);

    @Select("SELECT Film_name,Score,Url \n" +
            "FROM film_info JOIN film_broadcast_info\n" +
            "ON film_info.Film_id=film_broadcast_info.Film_id\n" +
            "WHERE film_info.Film_play_status=1\n" +
            "ORDER BY film_info.Score DESC\n")
    List<FilmItemShow> findAllOnlineFilms();
    @Select("SELECT Film_name,Score,Url \n" +
            "FROM film_info JOIN film_broadcast_info\n" +
            "ON film_info.Film_id=film_broadcast_info.Film_id\n" +
            "WHERE film_info.Film_play_status=0\n" +
            "ORDER BY film_info.Score DESC\n")
    List<FilmItemShow> findAllForthcomingFilms();
    @Select("SELECT Film_name,Score,Url \n" +
            "FROM film_info JOIN film_broadcast_info\n" +
            "ON film_info.Film_id=film_broadcast_info.Film_id\n" +
            "WHERE film_info.Film_play_status=1\n" +
            "ORDER BY film_info.Score DESC\n" +
            "LIMIT 5")
    List<FilmItemShow> findOnlineFilms();
    @Select("SELECT Film_name,Score,Url \n" +
            "FROM film_info JOIN film_broadcast_info\n" +
            "ON film_info.Film_id=film_broadcast_info.Film_id\n" +
            "WHERE film_info.Film_play_status=0\n" +
            "ORDER BY film_info.Score DESC\n" +
            "LIMIT 5")
    List<FilmItemShow> FindForthcomingFilms();
    @Select("SELECT Seat_info\n" +
            "FROM film_times\n" +
            "WHERE Film_times_id=#{0}")
    String findSeatInfoByTimesId(@Param("0") int time_id);




    @Select("SELECT f.* ,b.Url \n" +
            "FROM  film_info f JOIN film_broadcast_info b\n" +
            "ON f.Film_id=b.Film_id\n" +
            "WHERE f.Film_play_status=1\n" +
            "ORDER BY f.Score DESC\n")
    List<Film> findAllOnlineFilms_wx();
    @Select("SELECT f.* ,b.Url \n" +
            "FROM  film_info f JOIN film_broadcast_info b\n" +
            "ON f.Film_id=b.Film_id\n" +
            "WHERE f.Film_play_status=0\n" +
            "ORDER BY f.Score DESC\n")
    List<Film> findAllforthcomingFilms_wx();

    @Select("SELECT f.* ,b.Url \n" +
            "FROM  film_info f JOIN film_broadcast_info b\n" +
            "ON f.Film_id=b.Film_id\n" +
            "WHERE f.Film_id= #{0}")
    Film getFilmInfoById_wx(@Param("0") int film_id);
    @Select("SELECT *\n" +
            "FROM order_info\n" +
            "WHERE Uesr_id=(\n" +
            "SELECT Uesr_id\n" +
            "FROM tpp_user_info\n" +
            "WHERE User_name=#{0}\n" +
            ")")
    List<Order> getOrderInfoByUsername_wx(@Param("0") String username);

      @Select("SELECT film_times.*,hall_info.Hall_name,cinema_info.Cinema_name\n" +
              "FROM film_times JOIN hall_info\n" +
              "ON film_times.Hall_id=hall_info.Hall_id\n" +
              "JOIN cinema_info\n" +
              "ON hall_info.Cinema_id=cinema_info.Cinema_id\n" +
              "WHERE Film_id in (\n" +
              "SELECT Film_id\n" +
              "FROM film_info\n" +
              "WHERE Film_name=#{0}\n" +
              ")")
    List<FilmTime>getAllTimesByFilmName_wx(@Param("0")String filmName);

      @Select("SELECT Seat_info\n" +
              "FROM film_times\n" +
              "WHERE Film_times_id=#{0}\n")
    String getSeatInfoByTimeId_wx(@Param("0") int timeId);

}
